package com.gec.dao.impl;

import com.gec.dao.DocumentDao;
import com.gec.domain.Document;
import com.gec.domain.User;
import com.gec.utils.JDBCUtils;
import com.gec.utils.PageModel;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DocumentDaoImpl implements DocumentDao {
    /**查询全部文件*/
    @Override
    public List<Document> findAlldocument(Document document, PageModel model) {
        List<Document>documents=new ArrayList<>();

        //动态查询
        StringBuffer sql=new StringBuffer("SELECT d.*,d.user_id AS id,u.`username` FROM document_inf d LEFT JOIN user_inf u ON d.user_id=u.id WHERE 1=1 ");
        List<Object>objects=new ArrayList<>();

        ResultSet rs=null;
        try {
            //防止空指针异常
            if(document !=null && !"".equals(document.getFileName()) && document.getFileName() !=null){
                sql.append(" and filename like ?");
                objects.add("%"+document.getFileName()+"%");
            }
            //分页查询
            sql.append(" limit ").append(model.getStartRow()).append(",").append(PageModel.pageSize);

            //形成数组
            Object[] params = objects.toArray(new Object[objects.size()]);

            rs= JDBCUtils.executeQuery(sql.toString(),params);

            while(rs.next()){
                Document document1=new Document();
                copy(rs,document1);
                documents.add(document1);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.close(JDBCUtils.getConn(),JDBCUtils.getPs(),null);
        }
        return documents;
    }

    /**单个查询*/
    @Override
    public Document findById(int id) {
        String sql="SELECT * FROM document_inf WHERE id=?";
        Object[]params={id};
        ResultSet rs=null;
        try {
            rs=JDBCUtils.executeQuery(sql,params);
            while(rs.next()){
                Document document1=new Document();
                copy(rs,document1);
                return document1;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(JDBCUtils.getConn(),JDBCUtils.getPs(),null);
        }
        return null;
    }

    /**上传文件*/
    @Override
    public void addDocument(Document document) {
        String sql="INSERT INTO document_inf(title,filename,filetype,filebytes,remark,create_date,user_id)VALUES(?,?,?,?,?,?,?)";
        Object[] params={document.getTitle(),document.getFileName(),document.getFiletype(),document.getFileBytes(),document.getRemark(),document.getCreateDate(),document.getUser().getId()};
        try {
            JDBCUtils.executeUpdate(sql,params);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.close(JDBCUtils.getConn(),JDBCUtils.getPs(),null);
        }
    }

    @Override
    public int getTotalDocumentCount(Document document) {
        StringBuffer sql=new StringBuffer("SELECT COUNT(0) FROM document_inf WHERE 1=1  ");

        List<Object>objects=new ArrayList<>();
        ResultSet rs=null;
        try{
            if(document != null && !"".equals(document.getFileName()) && document.getFileName() !=null){
                sql.append("  and filename like ?");
                objects.add("%" +document.getFileName() +"%");
            }
            //分页查询
            //动态参数数组 转换成数组
            Object[]params=objects.toArray(new Object[objects.size()]);
            rs=JDBCUtils.executeQuery(sql.toString(),params);

            while (rs.next()){
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(JDBCUtils.getConn(),JDBCUtils.getPs(),rs);
        }
        return 0;
    }

    @Override
    public Document downDocument(String id) {
        String sql="SELECT * FROM document_inf WHERE id=?";
        Object[]params={id};
        ResultSet rs=null;
        Document document=null;
        try {
            rs = JDBCUtils.executeQuery(sql, params);

            document = new Document();
            rs.next();
            copy(rs,document);

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.close(JDBCUtils.getConn(),JDBCUtils.getPs(),null);
        }
        return document;
    }

    @Override
    public void deleteDocument(int id) {
        String sql="DELETE FROM document_inf WHERE id=?";
        Object[]params={id};
        try {
            JDBCUtils.executeUpdate(sql,params);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(JDBCUtils.getConn(),JDBCUtils.getPs(),null);
        }
    }

    private void copy(ResultSet rs, Document document1) throws SQLException {

        try {document1.setId(rs.getInt("ID"));
            document1.setTitle(rs.getString("TITLE"));
            document1.setFileName(rs.getString("filename"));
            document1.setFileBytes(rs.getBytes("filebytes"));
            document1.setFiletype(rs.getString("filetype"));
            document1.setRemark(rs.getString("REMARK"));
            document1.setCreateDate(rs.getDate("CREATE_DATE"));

            //获取字段数量 解决
            int columnCount = rs.getMetaData().getColumnCount();
            System.out.println("columnCount = " + columnCount);
            User user=new User();
            if(columnCount>8){
                user.setId(rs.getInt(9));
                user.setUsername(rs.getString(10));
            }
            document1.setUser(user);
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}
